import mysql from "mysql";
import SB from "./SqlBuilder";

/** 
 * mysql增删改查封装库
 * By kilomChou 
 */
namespace DB {

    /** 计数参数*/
    export interface ICountCfg {
        /** 表名 */
        table: string | string[],
        /** 条件 */
        where?: string | SB.IWhereCfg | SB.IWhereCfg[],
        /** 条件数据值 */
        whereVal?: any[],
    }

    /** 修改参数 */
    export interface IUpdateCfg {
        /** 表名 */
        table: string | string[],
        /** 数据值 */
        data: SB.IData,
        /** 条件 */
        where?: string | SB.IWhereCfg | SB.IWhereCfg[],
        /** 条件数据值 */
        whereVal?: any[],
    }

    /** 查询参数 */
    export interface IQueryCfg {
        /** 表名 */
        table: string | string[],
        /** 字段名 */
        field?: string[],
        /** 数据值 */
        val?: (string | number)[],
        /** 条件 */
        where?: string | SB.IWhereCfg | SB.IWhereCfg[],
        /** 条件数据值 */
        whereVal?: any[],
        /** 排序 */
        order?: string | SB.OrderCfg,
        /** 条数 */
        limit?: number,
        /** 页数 */
        page?: number
    }

    /** 插入参数 */
    export interface IInsertCfg {
        /** 表名 */
        table: string | string[],
        /** 数据值 */
        data: SB.IData,
        /** 忽略已存在插入的新记录 */
        ignore?: boolean
    }

    /** 删除参数接口 */
    export interface IDelCfg {
        /** 表名 */
        table: string | string[],
        /** 条件 */
        where: string | SB.IWhereCfg | SB.IWhereCfg[],
        /** 数据值 */
        whereVal: any[],
    }

    /** 连接池 */
    let _pool: mysql.Pool;

    /** 初始化数据库 */
    export function init(cfg: mysql.PoolConfig) {
        //创建连接池
        _pool = mysql.createPool(cfg);

        //进程退出时自动关闭连接池
        process.on("exit", async () => {
            try {
                await new Promise<void>((res, rej) => {
                    _pool.end(err => err ? rej(err) : res());
                })
            } catch (e) { }
        })
    }

    /** 获取连接池中的连接 */
    export function getConn() {
        return new Promise<mysql.PoolConnection>((res, rej) => {
            _pool.getConnection((err, conn) => {
                err ? rej(err) : res(conn);
            })
        })
    }

    /** 执行sql语句 */
    export function execute(sql: string, val?: any[], _conn?: mysql.PoolConnection) {
        return new Promise<any>(async (resolve, reject) => {
            let conn = _conn || await getConn()
            conn.query(sql, val, (err, res, field) => {
                err ? reject(err) : resolve(res);
            })
            if (!_conn) conn.release()
        })
    }

    /** 联合语句 */
    export function union(sqlArr: { sql: string, val: any[] }[], _conn?: mysql.PoolConnection) {
        let sqls = []
        let val = []
        sqlArr.forEach((sql, i) => {
            sqls[i] = `(${sql.sql})`
            val = val.concat(sql.val)
        })
        return execute(sqls.join(" UNION "), val, _conn)
    }
    
    /** 计数 */
    export async function count(cfg: ICountCfg, conn?: mysql.PoolConnection): Promise<number> {
        let table = SB.buildTable(cfg.table),
            where = SB.buildWhere(cfg.where, cfg.whereVal),
            sql = `SELECT COUNT(1) FROM ${table}${where.sql}`
        return (await execute(sql, where.val, conn))[0]['COUNT(1)']
    }

    /** 查询 */
    export function query(cfg: IQueryCfg, conn?: mysql.PoolConnection) {
        cfg.field && cfg.field.forEach((field, i) => cfg.field[i] = "`" + field + "`")
        let table = SB.buildTable(cfg.table),
            field = (cfg.field || ["*"]).join(","),
            where = SB.buildWhere(cfg.where, cfg.whereVal),
            order = SB.buildOrder(cfg.order),
            limit = SB.buildLimit(cfg.limit),
            sql = `SELECT ${field} FROM ${table}${where.sql}${order}${limit}`;
        return execute(sql, where.val, conn);
    }

    /** 返回查询sql语句 */
    export function query2sql(cfg: IQueryCfg) {
        cfg.field && cfg.field.forEach((field, i) => cfg.field[i] = "`" + field + "`")
        let table = SB.buildTable(cfg.table),
            field = (cfg.field || ["*"]).join(","),
            where = SB.buildWhere(cfg.where, cfg.whereVal),
            order = SB.buildOrder(cfg.order),
            limit = SB.buildLimit(cfg.limit),
            sql = `SELECT ${field} FROM ${table}${where.sql}${order}${limit}`;
        return { sql, val: where.val }
    }

    /** 查询一个 */
    export async function queryOne(cfg: IQueryCfg, conn?: mysql.PoolConnection) {
        return (await query(cfg, conn))[0];
    }

    /** 插入 */
    export function insert(cfg: IInsertCfg, conn?: mysql.PoolConnection) {
        let table = (cfg.table instanceof Array ? cfg.table : [cfg.table]).join(","),
            data = SB.dataHandle(cfg.data),
            field = data.field.join(","),
            val = data.val,
            ignore = cfg.ignore ? " IGNORE" : "",
            sql = `INSERT${ignore} INTO ${table} (${field}) VALUES(?)`;
        return execute(sql, [val], conn)
    }

    /** 更新 */
    export function update(cfg: IUpdateCfg, conn?: mysql.PoolConnection) {
        let table = (cfg.table instanceof Array ? cfg.table : [cfg.table]).join(","),
            data = SB.dataHandle(cfg.data),
            field = data.field.join('=?,') + '=?',
            val = data.val,
            where = SB.buildWhere(cfg.where, cfg.whereVal),
            sql = `UPDATE ${table} SET ${field}${where.sql} `
        return execute(sql, val.concat(where.val), conn)
    }

    /** 删除 */
    export function del(cfg: IDelCfg, conn?: mysql.PoolConnection) {
        let table = (cfg.table instanceof Array ? cfg.table : [cfg.table]).join(","),
            where = SB.buildWhere(cfg.where, cfg.whereVal),
            sql = `DELETE FROM ${table}${where.sql} `
        return execute(sql, where.val, conn)
    }
}
export default DB